﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Data.SQLite;
using System.Collections;
using easywork;


namespace easywork.Utils
{
    class DataBaseManager
    {
        /// <summary>
        /// 数据库文件名称
        /// </summary>
        public static string dictFileName = "test.db3";//ConfigManager.Config.AppSettings.Settings["dbName"].Value;//"test.db3";
        public static string dbPassword = "123456";

        private static SQLiteConnection _connection = null;
        public static string constr = @"data source=" + filepath + ";Version=3;";
        private static string _filepath = "";
        /// <summary>
        /// sqlLite数据库文件路径（含文件名称和后缀）
        /// </summary>
        private static string filepath
        {
            get
            {
                if (string.IsNullOrEmpty(_filepath))
                {
                    string path = AppDomain.CurrentDomain.BaseDirectory;
                    _filepath = System.IO.Path.Combine(path, dictFileName);

                }
                return _filepath;
            }
        }
        /// <summary>
        /// 数据库连接
        /// </summary>
        public static SQLiteConnection connection
        {
            get
            {
                if (_connection == null)
                {
                    _connection = new SQLiteConnection();
                    _connection.ConnectionString = constr;
                }
                
                return _connection;

            }
        }
        public static void CreateDB(string dbName)
        {
            string databaseFileName = System.Environment.CurrentDirectory + @"/" + dbName;

            SQLiteConnection.CreateFile(databaseFileName);
        }
        /*
        public static ArrayCollection queryData(String sql, ArrayList keyList)
        {
            using (SQLiteConnection cnn = new SQLiteConnection())
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    try
                    {
                    string path = AppDomain.CurrentDomain.BaseDirectory;
                    string filename = System.IO.Path.Combine(path, dictFileName);
                    //string cnnstr = @"data source=" + filename + ";Version=3;Password=" + dbPassword + ";";
                    string cnnstr = @"data source=" + filename + ";Version=3;";
                    LogHelper.WriteLine("数据库连接串："+cnnstr);

                    cnn.ConnectionString = cnnstr;
                    if (cnn.State != ConnectionState.Open)
                    {
                        cnn.Open();
                    }
                    LogHelper.WriteLine("SQL:" + sql);
                        cmd.CommandText = sql;
                        cmd.Connection = cnn;
                        SQLiteDataReader dataReader = cmd.ExecuteReader();

                        ArrayCollection list = new ArrayCollection();
                        while (dataReader.Read())
                        {
                            if (dataReader.FieldCount >= keyList.Count)
                            {
                                Dictionary<String, String> map = new Dictionary<string, string>();
                                int i = 0;
                                foreach (String key in keyList)
                                {
                                    map.Add(key, (dataReader[i] is DBNull) ? string.Empty : dataReader[i].ToString());
                                    i++;
                                }
                                list.Add(map);
                            }
                        }

                        return list;
                    }
                    catch (Exception ex)
                    {
                        LogHelper.TRACE(ex);
                        return null;
                    }
                }
            }
        }
        */

        public static bool executeNonQuery(string sql, ArrayList dataList)
        {

            using (SQLiteConnection cnn = new SQLiteConnection())
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    string path = AppDomain.CurrentDomain.BaseDirectory;
                    string filename = System.IO.Path.Combine(path, dictFileName);
                    //string cnnstr = @"data source=" + filename + ";Version=3;Password=" + dbPassword + ";";
                    string cnnstr = @"data source=" + filename + ";Version=3;";

                    cnn.ConnectionString = cnnstr;
                    if (cnn.State != ConnectionState.Open)
                    {
                        cnn.Open();
                    }

                    try
                    {
                        SQLiteTransaction tran = cnn.BeginTransaction();
                        cmd.CommandText = sql;
                        if (dataList != null && dataList.Count >= 5)
                        {
                            //cmd.Parameters.AddWithValue("$UUID", dataList[0]);
                            //cmd.Parameters.AddWithValue("$CONTENT", dataList[1]);
                            //cmd.Parameters.AddWithValue("$CATALOG", dataList[2]);
                            //cmd.Parameters.AddWithValue("$CTYPE", dataList[3]);
                            //cmd.Parameters.AddWithValue("$TITLE", dataList[4]);
                        }


                        cmd.Connection = cnn;
                        cmd.Transaction = tran;
                        int res = cmd.ExecuteNonQuery();

                        tran.Commit();
                        if (res >= 0)
                        {
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.StackTrace);
                        return false;
                    }

                }
            }
        }

        public static bool executeNonQuery(string sql)
        {

            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                try
                {
                    SQLiteTransaction tran = connection.BeginTransaction();
                    cmd.CommandText = sql;
                    cmd.Connection = connection;
                    cmd.Transaction = tran;
                    int res = cmd.ExecuteNonQuery();

                    tran.Commit();
                    if (res >= 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.StackTrace);
                    return false;
                }
            }

        }

        public static bool executeNonQuery(string[] sqls)
        {

            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                try
                {
                    SQLiteTransaction tran = connection.BeginTransaction();
                    int res=0;
                    foreach (string sql in sqls)
                    {
                        if (string.IsNullOrEmpty(sql)) continue;
                        cmd.CommandText = sql;
                        cmd.Connection = connection;
                        cmd.Transaction = tran;
                        res+=cmd.ExecuteNonQuery();
                    }
                    //cmd.CommandText = sql;
                    //cmd.Connection = connection;
                    //cmd.Transaction = tran;
                    //int res = cmd.ExecuteNonQuery();

                    tran.Commit();
                    if (res >= 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.StackTrace);
                    return false;
                }
            }

        }
        //执行查询返回DataSet
        /// <summary>
        /// 根据sql语句查询数据库数据
        /// </summary>
        /// <param name="sqlStr">查询用的sql语句</param>
        /// <returns></returns>
        public static DataSet ExecDataSet(string sqlStr)
        {
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            SQLiteCommand cmd = connection.CreateCommand();
            cmd.CommandText = sqlStr;
            cmd.CommandType = CommandType.Text;

            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds;
        }


    }
}
